package com.example.electricbicycle.dao;

import com.example.electricbicycle.po.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAOImpl implements UserDAO {

    @Override
    public User findUser(String username, String password) {
        try (Connection conn = DBUtils.getConnection()) {
            QueryRunner qr = new QueryRunner();
            // 修复字段名: password -> pass
            String sql = "SELECT * FROM user WHERE name = ? AND password = ?";
            return qr.query(conn, sql, new BeanHandler<>(User.class), username, password);
        } catch (SQLException e) {
            System.err.println("登录查询失败: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<User> findAllUsers() {
        try (Connection conn = DBUtils.getConnection()) {
            QueryRunner qr = new QueryRunner();
            String sql = "SELECT * FROM user";
            return qr.query(conn, sql, new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<User> findAllUsers(int page, int pageSize) {
        String sql = "SELECT id, name, password AS password, role FROM user LIMIT ? OFFSET ?";
        List<User> userList = new ArrayList<>();

        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int offset = (page - 1) * pageSize;
            pstmt.setInt(1, pageSize);
            pstmt.setInt(2, offset);

            System.out.println("[DAO] 执行分页查询: page=" + page + ", pageSize=" + pageSize + ", offset=" + offset);

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                // 修复字段映射：使用pass AS password
                user.setPassword(rs.getString("password"));
                user.setRole(rs.getInt("role"));
                userList.add(user);

                // 调试输出
                System.out.println("[DAO] 查询到用户: " + user.getId() + " - " + user.getName());
            }

            System.out.println("[DAO] 返回用户数量: " + userList.size());

        } catch (SQLException e) {
            System.err.println("分页查询失败: " + e.getMessage());
            e.printStackTrace();
        }

        return userList;
    }

    // 添加角色统计方法
    public int getUserCountByRole(int role) {
        String sql = "SELECT COUNT(*) FROM user WHERE role = ?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, role);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int getUserCount() {
        String sql = "SELECT COUNT(*) FROM user";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User findUserById(int id) {
        try (Connection conn = DBUtils.getConnection()) {
            QueryRunner qr = new QueryRunner();
            String sql = "SELECT * FROM user WHERE id = ?";
            return qr.query(conn, sql, new BeanHandler<>(User.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int addUser(User user) {
        try (Connection conn = DBUtils.getConnection()) {
            QueryRunner qr = new QueryRunner();
            // 修复字段名: password -> pass
            String sql = "INSERT INTO user (name, password, role) VALUES (?, ?, ?)";
            return qr.update(conn, sql, user.getName(), user.getPassword(), user.getRole());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int updateUser(User user) {
        try (Connection conn = DBUtils.getConnection()) {
            QueryRunner qr = new QueryRunner();
            // 修复字段名: password -> pass
            String sql = "UPDATE user SET name = ?, password = ?, role = ? WHERE id = ?";
            return qr.update(conn, sql,
                    user.getName(),
                    user.getPassword(),
                    user.getRole(),
                    user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int deleteUser(int id) {
        try (Connection conn = DBUtils.getConnection()) {
            QueryRunner qr = new QueryRunner();
            String sql = "DELETE FROM user WHERE id = ?";
            return qr.update(conn, sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public List<User> queryByRole(String role) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean checkUsernameExists(String username) {
        String sql = "SELECT COUNT(*) FROM user WHERE name = ?";
        try (Connection conn = DBUtils.getConnection()) {
            Long count = new QueryRunner().query(conn, sql, new ScalarHandler<>(), username);
            return count != null && count > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return true; // 失败时默认返回存在
        }
    }

    @Override
    public User findUserByUsername(String username) {
        // TODO Auto-generated method stub
        return null;
    }
}